package excel导出为vcf;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelToVcard {

	public static void main(String[] args) throws Exception {
		// String path = "E://新干事通讯录.xlsx";
		// Map<String, String> map = getRowAbove2007(path, 0, 1, 0);

		String path = "E://modelnew500.xls";
		Map<String, String> map = getRowBelow2007(path, 2, 7, 15);

		printMap(map);

		exportUserList("E://", "adf.vcf", map);

	}

	/**
	 * 
	 * 
	 * @param inpath
	 *            excel所在位置
	 * @param nameCol
	 *            名字所在列
	 * @param phoneCol
	 *            手机号所在列
	 * @param inRow
	 *            名字和手机号所在行
	 * @param outAbsPath
	 *            输出文件的路径
	 * @return
	 * @throws Exception
	 */
	public static Map<String, String> ReadAndOut(String inpath, int nameCol, int phoneCol, int inRow, String outAbsPath)
			throws Exception {

		Map<String, String> map = null;

		if (inpath.endsWith("xls")) {
			map = getRowBelow2007(inpath, nameCol, phoneCol, inRow);
		} else if (inpath.endsWith("xlsx")) {
			map = getRowAbove2007(inpath, nameCol, phoneCol, inRow);
		}

		if (map == null)
			throw new Exception("无法得到对应的行！！请确认输入正确！");

		int lastSplit = outAbsPath.lastIndexOf("\\");

		String outpath = outAbsPath.substring(0, lastSplit);

		System.out.println("outpath:" + outpath);
		String filename = outAbsPath.substring(lastSplit + 1);

		System.out.println("filename:" + filename);

		printMap(map);

		exportUserList(outpath, filename, map);

		return map;

	}

	// 得到联系人和电话号码
	public static Map<String, String> getRowAbove2007(String path, int nameCol, int phoneCol, int inRow)
			throws Exception {

		File file = new File(path);

		int index = 0;

		Map<String, String> map = new HashMap<String, String>();

		try {
			XSSFWorkbook workbook = new XSSFWorkbook(FileUtils.openInputStream(file));

			Sheet sheet = workbook.getSheetAt(0);

			int lastRowNum = sheet.getLastRowNum();

			for (int i = inRow; i < lastRowNum + 1; i++) {
				Row row = sheet.getRow(i);

				row.getCell(nameCol).setCellType(Cell.CELL_TYPE_STRING);
				String n = row.getCell(nameCol).getStringCellValue();
				row.getCell(phoneCol).setCellType(Cell.CELL_TYPE_STRING);
				String p = row.getCell(phoneCol).getStringCellValue();
				// 如果name是空，则不操作。
				if (n.trim().isEmpty()) {
					continue;
				}
				if (map.containsKey(n)) {
					System.out.println("此人存在重名:" + n);
					map.put(n + (++index), "+86" + p);
				} else
					map.put(n, "+86" + p);

			}

			workbook.close();

		} catch (IOException e) {
			map = null;
			e.printStackTrace();
			throw new Exception("文件中可能存在空单元格！");
		}

		return map;
	}

	// 得到联系人和电话号码
	public static Map<String, String> getRowBelow2007(String path, int nameCol, int phoneCol, int inRow)
			throws Exception {

		File file = new File(path);

		int index = 0;

		Map<String, String> map = new HashMap<String, String>();

		try {
			HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));

			HSSFSheet sheet = workbook.getSheetAt(0);

			int lastRowNum = sheet.getLastRowNum();

			for (int i = inRow; i < lastRowNum + 1; i++) {
				HSSFRow row = sheet.getRow(i);

				row.getCell(nameCol).setCellType(Cell.CELL_TYPE_STRING);
				String n = row.getCell(nameCol).getStringCellValue();
				// System.out.println(n);
				row.getCell(phoneCol).setCellType(Cell.CELL_TYPE_STRING);
				String p = row.getCell(phoneCol).getStringCellValue();
				// 如果name是空，则不操作。
				if (n.trim().isEmpty()) {
					continue;
				}
				if (map.containsKey(n)) {
					System.out.println("此人存在重名:" + n);
					map.put(n + (++index), "+86" + p);
				} else
					map.put(n, "+86" + p);

			}

			workbook.close();

		} catch (IOException e) {
			map = null;
			e.printStackTrace();
		}

		return map;
	}

	// 打印
	public static void printMap(Map<String, String> map) {
		int i = 1;

		if (map == null) {
			System.out.println("操作失误！");
		} else {
			for (String key : map.keySet()) {
				System.out.println((i++) + " " + key + " : " + map.get(key));
			}
		}
	}

	// 导出
	public static void exportUserList(String exportPath, String fileName, Map<String, String> map) throws Exception {

		createFile(exportPath, fileName);

		File file = new File(exportPath + fileName);

		file.setWritable(true);
		file.setExecutable(true);
		file.setReadable(true);
		StringBuilder sb = new StringBuilder();

		// String vcard = "BEGIN:VCARD" +
		// "\nVERSION:3.0" +
		// "\nN;CHARSET=UTF-8:赵;今;;;" +
		// "\nFN;CHARSET=UTF-8:赵今" +
		// "\nTEL;TYPE=CELL:+86189" +
		// "\nEND:VCARD";
		//
		// List<String> list = new ArrayList<String>();

		for (String key : map.keySet()) {
			String name = key;
			String phone = map.get(key);

			sb.append("BEGIN:VCARD\n");
			sb.append("VERSION:3.0\n");
			System.out.println("name.trim()-->" + name.trim());
			sb.append("N;CHARSET=UTF-8;ENCODING=QUOTED-PRINTABLE:"
					+ transferToQUOTED_PRINTABLE(name.trim().charAt(0) + "") + ";"
					+ transferToQUOTED_PRINTABLE(name.substring(1)) + ";;;\n");
			sb.append("FN;CHARSET=UTF-8;ENCODING=QUOTED-PRINTABLE:" + transferToQUOTED_PRINTABLE(name) + "\n");
			sb.append("TEL;TYPE=CELL:" + phone + "\n");
			sb.append("END:VCARD\n");

		}

		String finalString = sb.toString();

		try {
			FileWriter fr = new FileWriter(file);

			System.out.println(finalString);
			fr.write(finalString);

			fr.flush();
			fr.close();
			sb = null;
		} catch (FileNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			throw new Exception("文件未找到。");
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			throw new Exception("软件输入输出异常！");
		}

	}

	// 创建文件
	public static void createFile(String filepath, String filename) throws Exception {

		// path表示你所创建文件的路径
		String path = filepath;
		File f = new File(path);

		if (!f.exists()) {
			f.mkdirs();
			f.setReadable(true);
			f.setWritable(true);
			f.setExecutable(true);
		}
		// fileName表示你创建的文件名；为txt类型；
		String fileName = filename;
		File file = new File(f, fileName);
		if (!file.exists()) {
			try {
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
				throw new Exception("文件位置出错！请检查输出框中文件路径是否正确！");
			}
		}
		file.setExecutable(true);
		file.setReadable(true);
		file.setWritable(true);

	}

	/**
	 * 转换为QUOTED_PRINTABLE格式
	 * 
	 * @param s
	 * @return
	 * @throws UnsupportedEncodingException
	 */
	private static String transferToQUOTED_PRINTABLE(String s) throws UnsupportedEncodingException {

		return URLEncoder.encode(s, "UTF-8").replace('%', '=');

	}

}
